package dbAccess;

import java.sql.*;
import java.util.*;

import exceptions.*;

import model.Professeur;
import model.Syllabus;

public class SyllabusDBAccess {
	public ArrayList<Syllabus> getAllSyllabus(int annee, int section_id) throws PasIdentificationException, DatabaseErrorException{
		ArrayList<Syllabus> tabSyl = new ArrayList<Syllabus>();
		SectionDBAccess sdba = new SectionDBAccess();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query1 = "select * from syllabus, auteur, professeur where syllabus.syllabus_id=auteur.syllabus_id and auteur.professeur_id=professeur.professeur_id order by syllabus.section_id, syllabus.annee, syllabus.intitule;";
		String query2 = "select * from syllabus, auteur, professeur where syllabus.annee = ? and syllabus.syllabus_id=auteur.syllabus_id and auteur.professeur_id=professeur.professeur_id order by syllabus.section_id, syllabus.annee, syllabus.intitule;";
		String query3 = "select * from syllabus, auteur, professeur where syllabus.section_id = ? and syllabus.syllabus_id=auteur.syllabus_id and auteur.professeur_id=professeur.professeur_id order by syllabus.section_id, syllabus.annee, syllabus.intitule;";
		String query4 = "select * from syllabus, auteur, professeur where syllabus.annee = ? and syllabus.section_id = ? and syllabus.syllabus_id=auteur.syllabus_id and auteur.professeur_id=professeur.professeur_id order by syllabus.section_id, syllabus.annee, syllabus.intitule;";
		try {
			if(annee>0 || section_id>0){
				if(annee==0){
					stat = conn.prepareStatement(query3);
					stat.setInt(1, section_id);
				}
				else{
					if(section_id==0){
						stat = conn.prepareStatement(query2);
						stat.setInt(1, annee);
					}
					else {
						stat = conn.prepareStatement(query4);
						stat.setInt(1, annee);
						stat.setInt(2, section_id);
					}
				}
			}
			else stat = conn.prepareStatement(query1);
			
			ResultSet data = stat.executeQuery();
			while(data.next()){
				Syllabus s = new Syllabus(data.getInt("syllabus_id"), data.getString("intitule"), sdba.getSection(data.getInt("section_id")), data.getInt("annee"), data.getInt("nb_page"), true, false);
				tabSyl.add(s);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabSyl;
	}
	public Syllabus getSyllabus(int syllabus_id) throws PasIdentificationException, DatabaseErrorException{
		Syllabus syl = null;
		SectionDBAccess sdba = new SectionDBAccess();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = "select * from syllabus where syllabus_id = ?;";
		try {
			stat = conn.prepareStatement(query);
				stat.setInt(1, syllabus_id);
			ResultSet data = stat.executeQuery();
			
			while(data.next()){
				syl = new Syllabus(data.getInt("syllabus_id"), data.getString("intitule"), sdba.getSection(data.getInt("section_id")), data.getInt("annee"), data.getInt("nb_page"));
			}
			stat.close();
		} catch (SQLException e){
			throw new DatabaseErrorException(e);
		}
		return syl;
	}
	public int addSyllabus(Syllabus syl) throws PasIdentificationException, DatabaseErrorException{
		Connection conn = DBConnexionSingleton.getInstance();
		int syl_id=-1;
		PreparedStatement stat;
		String query;
		try {
			query = "select max(syllabus_id) from syllabus;";
			stat = conn.prepareStatement(query);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				syl_id = data.getInt(1);
			}
			stat.close();
			syl_id++;
			
			query = "insert into syllabus (intitule,section_id,annee,nb_page) values (?,?,?,?);";
			stat = conn.prepareStatement(query);
				stat.setString(1, syl.getIntitule());
				stat.setInt(2, syl.getSection().getSection_id());
				stat.setInt(3, syl.getAnnee());
				stat.setInt(4, syl.getNb_page());
			stat.executeUpdate();
			stat.close();
			
			query = "insert into auteur (syllabus_id,professeur_id) values (?,?);";
			stat = conn.prepareStatement(query);
				stat.setInt(1, syl_id);
				stat.setInt(2, syl.getAuteur().getId());
			stat.executeUpdate();
			stat.close();
			conn.commit();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return syl_id;
	}
	/**
	 * @param annee
	 * @param section_id
	 * @return
	 * @throws PasIdentificationException 
	 * @throws DatabaseErrorException 
	 */
	public ArrayList<Syllabus> getSyllabus(int annee, int section_id) throws PasIdentificationException, DatabaseErrorException {
		SectionDBAccess sdba = new SectionDBAccess();
		ArrayList<Syllabus> tabSyl = new ArrayList<Syllabus>();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query;
		try {
			query = "select * from syllabus, auteur, professeur where syllabus.annee = ? and syllabus.section_id = ? " +
					"and syllabus.syllabus_id=auteur.syllabus_id " +
					"and auteur.professeur_id=professeur.professeur_id order by syllabus.section_id, syllabus.annee, syllabus.intitule;";
			stat = conn.prepareStatement(query);
				stat.setInt(1, annee);
				stat.setInt(2, section_id);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				Syllabus s = new Syllabus(data.getInt("syllabus_id"), data.getString("intitule"), sdba.getSection(section_id), annee, data.getInt("nb_page"), true, false);
				String nom = data.getString("nom");
				String prenom = data.getString("prenom");
				s.setAuteur(new Professeur(data.getInt("professeur_id"), nom ,prenom));
				tabSyl.add(s);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabSyl;
	}
	public HashMap<Syllabus, Integer> getSyllabusCommandeEtu(String status) throws PasIdentificationException, DatabaseErrorException{
		HashMap<Syllabus, Integer> listing = new HashMap<Syllabus, Integer>();
		ArrayList<Syllabus> allSyllabus = getAllSyllabus(0, 0);
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = "select count(ligne_com_etu.syllabus_id) from syllabus, commande_etu, ligne_com_etu where syllabus.syllabus_id = ligne_com_etu.syllabus_id and ligne_com_etu.commande_etu_id=commande_etu.commande_etu_id and commande_etu.etat = ? and syllabus.syllabus_id = ?;";
		try{
			for(Syllabus s:allSyllabus){
				stat = conn.prepareStatement(query);
					stat.setString(1, status);
					stat.setInt(2, s.getSyllabus_id());
				ResultSet data = stat.executeQuery();
				while(data.next()){
					listing.put(s, new Integer(data.getInt(1)));
				}
				stat.close();
			}
		}catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return listing;
	}
}
